package br.com.fatecpg.minhasfinancas.sqlite;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.LinkedList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import br.com.fatecpg.minhasfinancas.model.AlertaModel;

public class AlertaSqlite extends SQLiteOpenHelper {

	public AlertaSqlite(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	private static final int DATABASE_VERSION = 4;
	private static final String DATABASE_NAME = "financas";

	@Override
	public void onCreate(SQLiteDatabase db) {

		String query = "CREATE TABLE tb_alerta ( "
				+ "cd_alerta INTEGER NOT NULL,  "
				+ "ds_alerta INTEGER NOT NULL,  " + "dt_alerta VARCHAR NULL,  "
				+ "dd_alerta_antecedencia VARCHAR NULL" + ");";

		db.execSQL(query);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// db.execSQL("DROP TABLE IF EXISTS tb_usuario");
		this.onCreate(db);
	}

	private static final String TABLE_ALERTA = "tb_alerta";
	private static final String KEY_ID = "cd_alerta";
	private static final String KEY_DESCRICAO = "ds_alerta";
	private static final String KEY_DATA = "dt_alerta";
	private static final String KEY_DIAS_ANTECEDENCIA = "dd_alerta_antecedencia";

	private static final String[] COLUMNS = { KEY_ID, KEY_DESCRICAO,
			KEY_DESCRICAO, KEY_DATA, KEY_DATA, KEY_DIAS_ANTECEDENCIA };

	public void Insert(AlertaModel model) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(KEY_DESCRICAO, model.getDescricao());
		values.put(KEY_DATA, model.getData().toString());
		values.put(KEY_DIAS_ANTECEDENCIA, model.getDiasAntecedencia());
		db.insert(TABLE_ALERTA, null, values);
		db.close();
	}

	public AlertaModel getAlerta(int id) throws ParseException {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.query(TABLE_ALERTA, // a. table
				COLUMNS, " id = ?", new String[] { String.valueOf(id) }, null, // e.
				null, // f. having
				null, // g. order by
				null); // h. limit

		if (cursor != null)
			cursor.moveToFirst();

		String dataStr = cursor.getString(2).toString();
		DateFormat fmt = new SimpleDateFormat("dd/MM/yyyy");
		java.sql.Date dataSQL = new java.sql.Date(fmt.parse(dataStr).getTime());
		AlertaModel model = new AlertaModel();
		model.setId(Integer.parseInt(cursor.getString(0)));
		model.setDescricao(cursor.getString(1));
		model.setData(dataSQL);
		model.setDiasAntecedencia(Integer.parseInt(cursor.getString(3)));

		return model;
	}

	public List<AlertaModel> getListaAlerta() throws ParseException {
		List<AlertaModel> alertas = new LinkedList<AlertaModel>();

		String query = "SELECT  * FROM " + TABLE_ALERTA;
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(query, null);

		AlertaModel despesa = null;
		if (cursor.moveToFirst()) {
			do {
				String dataStr = cursor.getString(2).toString();
				DateFormat fmt = new SimpleDateFormat("dd/MM/yyyy");
				java.sql.Date dataSQL = new java.sql.Date(fmt.parse(dataStr)
						.getTime());
				AlertaModel model = new AlertaModel();
				model.setId(Integer.parseInt(cursor.getString(0)));
				model.setDescricao(cursor.getString(1));
				model.setData(dataSQL);
				model.setDiasAntecedencia(Integer.parseInt(cursor.getString(3)));
				alertas.add(despesa);
			} while (cursor.moveToNext());
		}

		return alertas;
	}

	public int update(AlertaModel model) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(KEY_DESCRICAO, model.getDescricao());
		values.put(KEY_DATA, model.getData().toString());
		values.put(KEY_DIAS_ANTECEDENCIA, model.getDiasAntecedencia());
		int i = db.update(TABLE_ALERTA, values, KEY_ID + " = ?", // selections
				new String[] { String.valueOf(model.getId()) });
		db.close();
		return i;
	}

	public void delete(AlertaModel model) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_ALERTA, KEY_ID + " = ?",
				new String[] { String.valueOf(model.getId()) });
		db.close();
	}

}
